package com.adp.db;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.Vector;


/**
 * 此处插入类型描述。
 * 进行数据库写操作的类
 * 创建日期：(2002-8-9 13:53:37)
 * @author：Administrator
 */
public class DBOperator
{

    private static Log log= LogFactory.getLog(DBOperator.class.getName());

    public DBOperator()
    {
    }

    /**
     * 20200922 add
     * 执行指定的sql语句，返回结果集(该结果集Vector中放的是Hashtable对象)。
     * 用法：<br>
     * import java.util.*;
     * Vector v=dbo.dataSelect("select a,b from c where d");
     * String aa="";
     * String bb="";
     * for(int i=0;i<v.size();i++){
     *     Hashtable h=(Hashtable)v.elementAt(i);
     *     aa=h.get("a").toString();
     *     bb=h.get("b").toString();
     * }
     * @return java.util.Vector 该Vector中装的是Hashtable,没有结果集就返回一个空Vector（size()为0）
     * @param sql java.lang.String sql语句
     */
    public static Vector dataSelect(String datasource, String sql)
    {
        Connection con = DBCon.getCon(datasource);
        Vector v = new Vector();
        ResultSet rs = null;
        Statement st = null;
        try
        {
            st = con.createStatement();
            //sql = ConvertSql(sql);
            rs = st.executeQuery(sql);
            if(!putIntoV(v, sql, rs))
                log.info("get no data in dataSelect(String)");
        }catch(Throwable throwable)
        {
            throwable.printStackTrace(System.out);
            //System.out.println("===" + sql);
            log.error("data select error:" + throwable);
            log.error("sql:" + sql);
        }
        
        try
        {
            rs.close();
            st.close();
        }catch(Exception exception) { }
        
        try
        {
            DBCon.releaseCon(con);
        }
        catch(Exception exception1) { }
        return v;
    }
    
    /**
     * 20200901 add
     * 执行批量sql操作
     * 返回批处理执行结果。
     * @return boolean 返回值表示数据是否更新成功
     * @param sql java.lang.String[]
     * @param con 
     */

    public static boolean dataExec(String datasource, String sql[])
    {
        Connection con = null;
        Statement st = null;
        boolean flag = false;
        String mysql = "";
        try
        {
        	con = DBCon.getCon(datasource);
            con.setAutoCommit(false);
            st = con.createStatement();
            //把每句sql都加入到statement的队列中
            for(int i = 0; i < sql.length; i++){
            	mysql = sql[i];
            	st.executeUpdate(mysql);
            }

            flag = true;
        }
        catch(Throwable e1)
        {
            log.debug(mysql);
            log.error(e1);
        }
        try
        {
            if(flag)
                con.commit();
            else
                con.rollback();
        }
        catch(Throwable e2)
        {
            flag = false;
        }
        try
        {
            st.close();
        }
        catch(Throwable throwable) { }
        try
        {
            DBCon.releaseCon(con);
        }
        catch(Throwable throwable1) { }
        return flag;
    }

    /**
     * 执行批量sql操作
     * 返回批处理执行结果。
     * @return boolean 返回值表示数据是否更新成功
     * @param sql java.lang.String[]
     */

    public static boolean dataExec(String sql[])
    {
        Connection con = null;
        Statement st = null;
        boolean flag = false;
        String mysql = "";
        try
        {
            con = DBCon.getCon();
            con.setAutoCommit(false);
            st = con.createStatement();
            //把每句sql都加入到statement的队列中
            for(int i = 0; i < sql.length; i++){
            	mysql = sql[i];
            	st.executeUpdate(mysql);
            }

            flag = true;
        }
        catch(Throwable e1)
        {
            log.debug(mysql);
            log.error(e1);
        }
        try
        {
            if(flag)
                con.commit();
            else
                con.rollback();
        }
        catch(Throwable e2)
        {
            flag = false;
        }
        try
        {
            st.close();
        }
        catch(Throwable throwable) { }
        try
        {
            DBCon.releaseCon(con);
        }
        catch(Throwable throwable1) { }
        return flag;
    }

    /**
     * 执行单条sql操作
     * 返回执行结果。
     * 创建日期：(2002-12-10 14:13:58)
     * @param sql java.lang.String 待执行的sql语句
     * @return boolean 是否成功执行
     */
    public static boolean dataExec(String sql)
    {
        Connection con = null;
        Statement st = null;
        boolean flag = false;
        try
        {
            con = DBCon.getCon();
            st = con.createStatement();
            log.debug(sql);
            //sql = ConvertSql(sql);
            st.executeUpdate(sql);
            flag = true;
        }
        catch(Throwable e1)
        {
            log.error(e1);
        }
        try
        {
            st.close();
        }
        catch(Throwable throwable) { }
        try
        {
            DBCon.releaseCon(con);
        }
        catch(Throwable throwable1) { }
        return flag;
    }

    /**
     * 执行单条sql操作
     * 返回执行结果。
     * 创建日期：(2002-12-10 14:13:58)
     * @param sql java.lang.String 待执行的sql语句
     * @return boolean 是否成功执行
     */
    public static boolean dataExec(Connection con, String sql)
    {
        Statement st = null;
        boolean flag = false;
        try
        {
            st = con.createStatement();
            //sql = ConvertSql(sql);
            st.executeUpdate(sql);
            flag = true;
        }
        catch(Throwable e1)
        {
            log.error(e1);
        }
        try
        {
            st.close();
        }
        catch(Throwable throwable) { }
        return flag;
    }

    /**
     * 执行指定的sql语句，返回结果集(该结果集Vector中放的是Hashtable对象)。
     * 用法：<br>
     * import java.util.*;
     * Vector v=dbo.dataSelect("select a,b from c where d");
     * String aa="";
     * String bb="";
     * for(int i=0;i<v.size();i++){
     *     Hashtable h=(Hashtable)v.elementAt(i);
     *     aa=h.get("a").toString();
     *     bb=h.get("b").toString();
     * }
     * @return java.util.Vector 该Vector中装的是Hashtable,没有结果集就返回一个空Vector（size()为0）
     * @param sql java.lang.String sql语句
     */
    public static Vector dataSelect(String sql)
    {
        Connection con = DBCon.getCon();
        Vector v = new Vector();
        ResultSet rs = null;
        Statement st = null;
        try
        {
            st = con.createStatement();
            //sql = ConvertSql(sql);
            rs = st.executeQuery(sql);
            if(!putIntoV(v, sql, rs))
                log.info("get no data in dataSelect(String)");
        }catch(Throwable throwable)
        {
            throwable.printStackTrace(System.out);
           // System.out.println("===" + sql);
            log.error("data select error:" + throwable);
            log.error("sql:" + sql);
        }
        
        try
        {
            rs.close();
            st.close();
        }catch(Exception exception) { }
        
        try
        {
            DBCon.releaseCon(con);
        }
        catch(Exception exception1) { }
        return v;
    }
    
    private static boolean putIntoV(Vector v, String sql, ResultSet rs)
    {
        boolean flag = false;
        if(v == null)
        {
            log.error("in DBOperator.putIntoV(Vector,String,re),v can not be null!");
            return false;
        }
        try
        {
            ResultSetMetaData rsm = rs.getMetaData();
            int column_count = rsm.getColumnCount();
            int isqltype = getSQLType();
            while(rs.next()) 
            {
              //  HashtableExtend hee = putIntoHash(rsm, rs, isqltype);
                //v.add(hee);
                flag = true;
            }
        }
        catch(Throwable throwable)
        {
            log.error("data select error:" + throwable);
            log.error("sql:" + sql);
            flag = false;
        }
        return flag;
    }
    


    public static int getSQLType()
    {
        int sql_type = 0;
        //TODO: 此处返回数据库类型，待处理
        sql_type = 2;		//SQL Server
        return sql_type;
    }
    
/**提供字符串切割方法
 *@param source是要进行切割的字符串
 *@param spliter是用于切割的分隔符
 *@return 返回切割后生成的字符串数组
 *@exception 捕获到异常,返回长度为0的字符串数组
 */
public static String[] split(String source, String spliter) {
    try {
        int icount = 0, jcount = 0;
        for (int i = 0, j = source.length(); i <= j; i++) {
            icount = source.indexOf(spliter, i);
            if (icount != -1) {
                i = icount;
                jcount++;
            }
        }
        String targetArray[] = new String[jcount + 1];
        icount = 0;
        jcount = 0;
        for (int i = 0, j = targetArray.length; i < j; i++) {
            int itemp = source.indexOf(spliter, jcount);
            if (itemp != -1) {
                targetArray[i] = source.substring(jcount, itemp);
                jcount = itemp + spliter.length();
            } else {
                targetArray[i] = source.substring(jcount, source.length());
            }
        }
        return targetArray;
    } catch (Exception e) {
        e.printStackTrace();
        return new String[0];
    }
}

/**
 *对某段字符串中的制定字符串进行替换
 * @param material 要进行替换操作的字符串
 * @param regex 被替换的字符串
 * @param replacement 用来替换的字符串
 * @return 返回替换后的字符串
 * @exception 捕获异常后，返回空字符串
 */
public static String replaceStr(
    String material,
    String regex,
    String replacement) {
    try {
      String strArray[] = split(material, regex);
      StringBuffer sb = new StringBuffer(strArray[0]);

      if (strArray.length > 1)
        sb.append(replacement);

      for (int i = 1, j = strArray.length; i < j; i++) {

        sb.append(strArray[i]);
        if (i < strArray.length - 1) {
          sb.append(replacement);
        }
      }
      return sb.toString();
    }
    catch (Exception e) {
      e.printStackTrace();
        return "";
    }
}
   static String TransSql(String sql, int target_type) {
     switch (target_type) {

       case 1://将sql语句替换成oracle版本，类中已经写成oracle版本，无需转变。
         break;

       case 2://将sql语句替换成sqlserver版本
         sql=replaceStr(sql,"LENGTH","LEN");
         sql=replaceStr(sql,"length","LEN");//将求字符长度的函数替换

         sql=replaceStr(sql,"TO_NUMBER(","CONVERT(FLOAT,");
         sql=replaceStr(sql,"to_number(","CONVERT(FLOAT,");//替换字符转换函数

         sql=replaceStr(sql,"TO_DATE(","CONVERT(DATETIME,");
         sql=replaceStr(sql,"to_date(","CONVERT(DATETIME,");
         sql=replaceStr(sql,",'YYYY-MM-DD'","");//替换日期转换函数
         sql=replaceStr(sql,",'yyyy-mm-dd'","");//替换日期转换函数

         break;
     }
     return sql;
 }


   public static Vector dataSelect(String sql, int pageNum, int pageSize) {
   	// TODO Auto-generated method stub
	//2014-10-30 add 
   	return null;
   }

}
